﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Common;
using Qianli.DirSir.Model;
using System.Data;
using System.Data.SqlClient;

namespace Qianli.DirSir.DAL
{
    /// <summary>
    /// 采购订单表
    /// </summary>]
    public class StockOrderInfoDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 获取供应商信息，填充到下拉列表中
        /// </summary>
        /// <returns></returns>
        public List<ProviderInfo> GetProviderInfo()
        {
            string sql = "select ProviderID,ProviderName from ProviderInfo";
            DataTable dt = helper.ExecDataTable(sql);
            List<ProviderInfo> list = new List<ProviderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                ProviderInfo provider = new ProviderInfo();
                provider.ProviderID = Convert.ToInt32(item["ProviderID"]);
                provider.ProviderName = item["ProviderName"].ToString();
                list.Add(provider);
            }
            return list;
        }
        /// <summary>
        /// 采购订单显示
        /// </summary>
        /// <returns></returns>
        public List<StockOrderInfo> StockOrderInfoShow()
        {
            string sql = "select StockOrderID,ProviderInfo.ProviderName,StorehouseName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from ";
            sql += " StockOrderInfo ";
            sql += " join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ";
            sql += " join StorehouseInfo on StockOrderInfo.StorehouseID=StorehouseInfo.StorehouseID where StockOrderState='True'";
            DataTable dt = helper.ExecDataTable(sql);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo StockOrderInfo = new StockOrderInfo();
                StockOrderInfo.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                StockOrderInfo.ProviderName = item["ProviderName"].ToString();
                StockOrderInfo.StorehouseName = item["StorehouseName"].ToString();
                StockOrderInfo.StockOrderTime = item["StockOrderTime"].ToString();
                StockOrderInfo.DocumentsCoding = item["DocumentsCoding"].ToString();
                StockOrderInfo.Remarks = item["Remarks"].ToString();
                StockOrderInfo.LastModified = item["LastModified"].ToString();
                list.Add(StockOrderInfo);
            }
            return list;
        }
        /// <summary>
        /// 采购订单添加
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public List<int> AddStockOrderInfo(StockOrderInfo s, List<PurchaseOrderSheetInfo> listPurchaseOrderSheetInfo)
        {
            if (s.DocumentsCoding == "")
            {
                s.DocumentsCoding = System.Guid.NewGuid().ToString();
            }
            s.StockOrderTime = DateTime.Now.ToShortDateString();
            s.LastModified = DateTime.Now.ToString();
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            StringBuilder strSql = new StringBuilder();
            //Author: JiXiang 20121128
            //Description：添加采购单表结构
            //Modify Begin
            strSql.Append("insert into StockOrderInfo(");
            strSql.Append("ProviderID,StorehouseID,StockOrderTime,DocumentsCoding,Remarks,LastModified,StockOrderState)");
            strSql.Append(" values(@ProviderID,@StorehouseID,@StockOrderTime,@DocumentsCoding,@Remarks,@LastModified,@StockOrderState);select @@identity");
            SqlParameter[] parameters = {
                 new SqlParameter("@ProviderID",SqlDbType.Int),
                 new SqlParameter("@StorehouseID",SqlDbType.Int),
                 new SqlParameter("@StockOrderTime",SqlDbType.Date),
                 new SqlParameter("@DocumentsCoding",SqlDbType.NVarChar,50),
                 new SqlParameter("@Remarks",SqlDbType.NVarChar,20),
                 new SqlParameter("@LastModified",SqlDbType.DateTime),
                 new SqlParameter("@StockOrderState",SqlDbType.Bit)
                                        };
            parameters[0].Value = s.ProviderID;
            parameters[1].Value = s.StorehouseID;
            parameters[2].Value = s.StockOrderTime;
            parameters[3].Value = s.DocumentsCoding;
            parameters[4].Value = s.Remarks;
            parameters[5].Value = s.LastModified;
            parameters[6].Value = "True";
            //cmdTextList.Add(strSql.ToString());
            //cmdParametersList.Add(parameters.ToList<SqlParameter>());
            //helper.ExecScalar(strSql.ToString(), parameters);
            //Modify End
            int StockOrderID = Convert.ToInt32(helper.ExecScalar(strSql.ToString(), parameters));

            //子表添加数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder strSqlSub = new StringBuilder();
            strSqlSub.Append("insert into PurchaseOrderSheetInfo(");
            strSqlSub.Append(" GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetmoney)");
            strSqlSub.Append(" values(@GoodsID,@StockOrderID,@GoodsNum,@PurchaseOrderSheetmoney)");
            //非空判定
            //Author: ZhaoLiang 22012-04-25
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo m in listPurchaseOrderSheetInfo)
                {
                    m.StockOrderID = StockOrderID;
                    SqlParameter[] parametersSub = {
                         new SqlParameter("@GoodsID", SqlDbType.Int),
                         new SqlParameter("@StockOrderID", SqlDbType.Int),
                         new SqlParameter("@GoodsNum", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetmoney", SqlDbType.Decimal),
                                                       };
                    parametersSub[0].Value = m.GoodsID;
                    parametersSub[1].Value = m.StockOrderID;
                    parametersSub[2].Value = m.GoodsNum;
                    parametersSub[3].Value = m.PurchaseOrderSheetmoney;
                    //Modify End
                    cmdTextList.Add(strSqlSub.ToString());
                    cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
                }
            }
            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 采购订单表删除
        /// </summary>
        /// <returns>删除的时候先判断子表里面是否存在数据，存在执行子表删除，再执行主表删除，否则直接执行主表删除</returns>
        public List<int> DelStockOrderInfo(int StockOrderID)
        {
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            //查看子表里是否有记录
            StringBuilder getstrSql = new StringBuilder();
            getstrSql.Append("select PurchaseOrderSheetID,GoodsName,StockOrderID,GoodsNum,PurchaseOrderSheetmoney,TypeName ");
            getstrSql.Append(" from PurchaseOrderSheetInfo join GoodsInfo on PurchaseOrderSheetInfo.GoodsID=GoodsInfo.GoodsID");
            getstrSql.Append(" join GoodsTypeInfo on GoodsInfo.TypeID=GoodsTypeInfo.TypeID where StockOrderID=@StockOrderID");

            SqlParameter[] getparametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            getparametersSub[0].Value = StockOrderID;

            DataTable dt = helper.ExecDataTable(getstrSql.ToString(), getparametersSub);
            List<PurchaseOrderSheetInfo> list = new List<PurchaseOrderSheetInfo>();
            foreach (DataRow item in dt.Rows)
            {
                PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
                p.PurchaseOrderSheetID = Convert.ToInt32(item["PurchaseOrderSheetID"]);
                p.GoodsName = item["GoodsName"].ToString();
                p.TypeName = item["TypeName"].ToString();
                p.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(item["GoodsNum"]);
                p.PurchaseOrderSheetmoney = Convert.ToDecimal(item["PurchaseOrderSheetmoney"]);
                list.Add(p);
            }
            if (list.Count != 0)
            {
                //采购订单子表删除
                StringBuilder strSqlSub = new StringBuilder();
                strSqlSub.Append("delete from PurchaseOrderSheetInfo ");
                strSqlSub.Append("  where StockOrderID=@StockOrderID");

                SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
                parametersSub[0].Value = StockOrderID;

                cmdTextList.Add(strSqlSub.ToString());
                cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
            }
            //采购订单主表删除
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from StockOrderInfo");
            strSql.Append("  where StockOrderID=@StockOrderID");

            SqlParameter[] SqlParameters = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            SqlParameters[0].Value = StockOrderID;

            cmdTextList.Add(strSql.ToString());
            cmdParametersList.Add(SqlParameters.ToList<SqlParameter>());

            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 查看订单子表详细信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public List<PurchaseOrderSheetInfo> GetlistPurchaseOrderSheetInfoByStockOrderID(int StockOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select PurchaseOrderSheetID,GoodsInfo.GoodsID,GoodsInfo.GoodsCoding,GoodsName,StockOrderID,GoodsNum,PurchaseOrderSheetmoney ");
            strSql.Append(" from PurchaseOrderSheetInfo join GoodsInfo on PurchaseOrderSheetInfo.GoodsID=GoodsInfo.GoodsID");
            strSql.Append(" where StockOrderID=@StockOrderID");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = StockOrderID;

            DataTable dt = helper.ExecDataTable(strSql.ToString(), parametersSub);
            List<PurchaseOrderSheetInfo> list = new List<PurchaseOrderSheetInfo>();
            foreach (DataRow item in dt.Rows)
            {
                PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
                p.GoodsID = Convert.ToInt32(item["GoodsID"]);
                p.PurchaseOrderSheetID = Convert.ToInt32(item["PurchaseOrderSheetID"]);
                p.GoodsCoding = item["GoodsCoding"].ToString();
                p.GoodsName = item["GoodsName"].ToString();
                p.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(item["GoodsNum"]);
                p.PurchaseOrderSheetmoney = Convert.ToDecimal(item["PurchaseOrderSheetmoney"]);
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 查看入库订单子表详细信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public List<PurchaseOrderSheetInfo> GetlistPurByStockOrderID(int StockOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select PurchaseOrderSheetID,GoodsInfo.GoodsID,GoodsInfo.GoodsCoding,GoodsName,StockOrderID,GoodsNum,PurchaseOrderSheetmoney,PurchaseOrderSheetNum ");
            strSql.Append(" from PurchaseOrderSheetInfo join GoodsInfo on PurchaseOrderSheetInfo.GoodsID=GoodsInfo.GoodsID");
            strSql.Append(" where StockOrderID=@StockOrderID");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = StockOrderID;

            DataTable dt = helper.ExecDataTable(strSql.ToString(), parametersSub);
            List<PurchaseOrderSheetInfo> list = new List<PurchaseOrderSheetInfo>();
            foreach (DataRow item in dt.Rows)
            {
                PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
                p.GoodsID = Convert.ToInt32(item["GoodsID"]);
                p.PurchaseOrderSheetID = Convert.ToInt32(item["PurchaseOrderSheetID"]);
                p.GoodsCoding = item["GoodsCoding"].ToString();
                p.GoodsName = item["GoodsName"].ToString();
                p.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(item["GoodsNum"]);
                p.PurchaseOrderSheetmoney = Convert.ToDecimal(item["PurchaseOrderSheetmoney"]);
                p.PurchaseOrderSheetNum = Convert.ToInt32(item["PurchaseOrderSheetNum"]);
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 获取采购订单主表信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public List<StockOrderInfo> GetStockOrderByStockOrderID(int StockOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select StockOrderID,ProviderInfo.ProviderName,StorehouseName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from ");
            strSql.Append(" StockOrderInfo ");
            strSql.Append(" join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ");
            strSql.Append(" join StorehouseInfo on StockOrderInfo.StorehouseID=StorehouseInfo.StorehouseID where StockOrderID=@StockOrderID");
            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = StockOrderID;
            DataTable dt = helper.ExecDataTable(strSql.ToString(), parametersSub);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo StockOrderInfo = new StockOrderInfo();
                StockOrderInfo.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                StockOrderInfo.ProviderName = item["ProviderName"].ToString();
                StockOrderInfo.StorehouseName = item["StorehouseName"].ToString();
                StockOrderInfo.StockOrderTime = item["StockOrderTime"].ToString();
                StockOrderInfo.DocumentsCoding = item["DocumentsCoding"].ToString();
                StockOrderInfo.Remarks = item["Remarks"].ToString();
                StockOrderInfo.LastModified = item["LastModified"].ToString();
                list.Add(StockOrderInfo);
            }
            return list;
        }
        /// <summary>
        /// 修改采购订单信息
        /// </summary>
        /// <returns></returns>
        public List<int> UpdatePurchaseOrderSheetInfo(StockOrderInfo s, List<PurchaseOrderSheetInfo> listPurchaseOrderSheetInfo)
        {
            s.LastModified = DateTime.Now.ToString();
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            //订单主表最后修改时间修改
            StringBuilder strsql = new StringBuilder();
            strsql.Append("update StockOrderInfo set ");
            strsql.Append("  ProviderID=@ProviderID,CityID=@CityID,Remarks=@Remarks,LastModified=@LastModified where StockOrderID=@StockOrderID");
            SqlParameter[] updsqlparmeter ={
                                             new SqlParameter("@ProviderID",SqlDbType.Int),
                                             new SqlParameter("@StorehouseID",SqlDbType.Int),
                                             new SqlParameter("@Remarks",SqlDbType.NVarChar,20),
                                             new SqlParameter("@LastModified",SqlDbType.DateTime),
                                             new SqlParameter("@StockOrderID",SqlDbType.Int)
                                         };
            updsqlparmeter[0].Value = s.ProviderID;
            updsqlparmeter[1].Value = s.StorehouseID;
            updsqlparmeter[2].Value = s.Remarks;
            updsqlparmeter[3].Value = s.LastModified;
            updsqlparmeter[4].Value = s.StockOrderID;
            cmdTextList.Add(strsql.ToString());
            cmdParametersList.Add(updsqlparmeter.ToList<SqlParameter>());

            //采购订单子表删除
            StringBuilder delstrSqlSub = new StringBuilder();
            delstrSqlSub.Append("delete from PurchaseOrderSheetInfo ");
            delstrSqlSub.Append("  where StockOrderID=@StockOrderID");

            SqlParameter[] delparametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            delparametersSub[0].Value = s.StockOrderID;

            cmdTextList.Add(delstrSqlSub.ToString());
            cmdParametersList.Add(delparametersSub.ToList<SqlParameter>());
            //采购订单子表重填数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder addstrSqlSub = new StringBuilder();
            addstrSqlSub.Append("insert into PurchaseOrderSheetInfo(");
            addstrSqlSub.Append(" GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetmoney)");
            addstrSqlSub.Append(" values(@GoodsID,@StockOrderID,@GoodsNum,@PurchaseOrderSheetmoney)");
            //非空判定
            //Author: ZhaoLiang 22012-04-25
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo m in listPurchaseOrderSheetInfo)
                {
                    m.StockOrderID = s.StockOrderID;
                    SqlParameter[] addparametersSub = {
                         new SqlParameter("@GoodsID", SqlDbType.Int),
                         new SqlParameter("@StockOrderID", SqlDbType.Int),
                         new SqlParameter("@GoodsNum", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetmoney", SqlDbType.Int),
                                                       };
                    addparametersSub[0].Value = m.GoodsID;
                    addparametersSub[1].Value = m.StockOrderID;
                    addparametersSub[2].Value = m.GoodsNum;
                    addparametersSub[3].Value = m.PurchaseOrderSheetmoney;
                    //Modify End
                    cmdTextList.Add(addstrSqlSub.ToString());
                    cmdParametersList.Add(addparametersSub.ToList<SqlParameter>());
                }
            }
            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 根据采购订单模糊查询
        /// </summary>
        /// <param name="StockOrderName"></param>
        /// <returns></returns>
        public List<StockOrderInfo> GetStockOrderByStockOrderName(string DocumentsCoding)
        {
            StringBuilder StrSql = new StringBuilder();
            StrSql.Append("select StockOrderID,ProviderInfo.ProviderName,StorehouseName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from StockOrderInfo ");
            StrSql.Append(" join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ");
            StrSql.Append(" join StorehouseInfo on StockOrderInfo.StorehouseID=StorehouseInfo.StorehouseID where StockOrderState='True' and DocumentsCoding like @DocumentsCoding");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@DocumentsCoding",SqlDbType.NVarChar,50)  
                                           };
            parametersSub[0].Value = "%" + DocumentsCoding + "%";

            DataTable dt = helper.ExecDataTable(StrSql.ToString(), parametersSub);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo s = new StockOrderInfo();
                s.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                s.ProviderName = item["ProviderName"].ToString();
                s.StorehouseName = item["StorehouseName"].ToString();
                s.StockOrderTime = item["StockOrderTime"].ToString();
                s.DocumentsCoding = item["DocumentsCoding"].ToString();
                s.Remarks = item["Remarks"].ToString();
                s.LastModified = item["LastModified"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 审核入库
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <param name="listPurchaseOrderSheetInfo"></param>
        /// <returns></returns>
        public List<int> UpdPurchaseOrderSheetInfoByStockOrderID(int StockOrderID, List<PurchaseOrderSheetInfo> listPurchaseOrderSheetInfo)
        {
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();

            //采购订单子表删除
            StringBuilder delstrSqlSub = new StringBuilder();
            delstrSqlSub.Append("delete from PurchaseOrderSheetInfo ");
            delstrSqlSub.Append("  where StockOrderID=@StockOrderID");

            SqlParameter[] delparametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            delparametersSub[0].Value = StockOrderID;

            cmdTextList.Add(delstrSqlSub.ToString());
            cmdParametersList.Add(delparametersSub.ToList<SqlParameter>());
            //采购订单子表重填数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder addstrSqlSub = new StringBuilder();
            addstrSqlSub.Append("insert into PurchaseOrderSheetInfo(");
            addstrSqlSub.Append("GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetmoney,PurchaseOrderSheetNum)");
            addstrSqlSub.Append(" values(@GoodsID,@StockOrderID,@GoodsNum,@PurchaseOrderSheetmoney,@PurchaseOrderSheetNum)");
            //非空判定
            //Author: ZhaoLiang 22012-04-25
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo m in listPurchaseOrderSheetInfo)
                {
                    m.StockOrderID = StockOrderID;
                    SqlParameter[] addparametersSub = {
                         new SqlParameter("@GoodsID", SqlDbType.Int),
                         new SqlParameter("@StockOrderID", SqlDbType.Int),
                         new SqlParameter("@GoodsNum", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetmoney", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetNum",SqlDbType.Int)
                                                       };
                    addparametersSub[0].Value = m.GoodsID;
                    addparametersSub[1].Value = m.StockOrderID;
                    addparametersSub[2].Value = m.GoodsNum;
                    addparametersSub[3].Value = m.PurchaseOrderSheetmoney;
                    addparametersSub[4].Value = m.PurchaseOrderSheetNum;
                    //Modify End
                    cmdTextList.Add(addstrSqlSub.ToString());
                    cmdParametersList.Add(addparametersSub.ToList<SqlParameter>());
                }
            }
            //根据采购订单主表编号获取仓库编号和子表的商品编号和商品数量 根据商品编号和仓库编号进行库存信息的增加
            StringBuilder updInvet = new StringBuilder();
            updInvet.Append("update InventoryInformationInfo set InventoryInformationNum=");
            updInvet.Append("(select InventoryInformationNum from InventoryInformationInfo where StorehouseID=@StorehouseID and GoodsID=@GoodsID)+@PurchaseOrderSheetNum ");
            updInvet.Append("  where StorehouseID=@StorehouseID and GoodsID=@GoodsID");
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo item in listPurchaseOrderSheetInfo)
                {
                    SqlParameter[] updsqlparameter ={
                                                       new SqlParameter("@StorehouseID",SqlDbType.Int),
                                                       new SqlParameter("@GoodsID",SqlDbType.Int),
                                                       new SqlParameter("@PurchaseOrderSheetNum",SqlDbType.Int)
                                                   };
                    updsqlparameter[0].Value = GetStorehouseIDByStorehouse(StockOrderID).StorehouseID;
                    updsqlparameter[1].Value = item.GoodsID;
                    updsqlparameter[2].Value = item.PurchaseOrderSheetNum;
                    cmdTextList.Add(updInvet.ToString());
                    cmdParametersList.Add(updsqlparameter.ToList<SqlParameter>());
                }
            }
            //订单主表状态修改
            StringBuilder updstock = new StringBuilder();
            updstock.Append("update StockOrderInfo set StockOrderState='false' where StockOrderID=@StockOrderID");
            SqlParameter[] Updstocksqlparameter ={
                                                    new SqlParameter("@StockOrderID",SqlDbType.Int)
                                                };
            Updstocksqlparameter[0].Value = StockOrderID;
            cmdTextList.Add(updstock.ToString());
            cmdParametersList.Add(Updstocksqlparameter.ToList<SqlParameter>());

            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 根据采购订单子表编号获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PurchaseOrderSheetInfo GetPurchaseOrderSheetByPurchaseOrderSheetID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetmoney");
            strSql.Append("  from PurchaseOrderSheetInfo where PurchaseOrderSheetID=@id");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@id",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = id;

            SqlDataReader sdr = helper.ExecReader(strSql.ToString(), parametersSub);
            PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
            while (sdr.Read())
            {
                p.GoodsID = Convert.ToInt32(sdr["GoodsID"]);
                p.StockOrderID = Convert.ToInt32(sdr["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(sdr["GoodsNum"]);
                p.PurchaseOrderSheetmoney = Convert.ToDecimal(sdr["PurchaseOrderSheetmoney"]);
            }
            return p;
        }
        /// <summary>
        /// 根据采购订单编号获取仓库编号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public StockOrderInfo GetStorehouseIDByStorehouse(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select StorehouseID");
            strSql.Append("  from StockOrderInfo where StockOrderID=@id");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@id",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = id;

            SqlDataReader sdr = helper.ExecReader(strSql.ToString(), parametersSub);
            StockOrderInfo p = new StockOrderInfo();
            while (sdr.Read())
            {
                p.StorehouseID = Convert.ToInt32(sdr["StorehouseID"]);
            }
            return p;
        }

        /// <summary>
        /// 入库订单显示
        /// </summary>
        /// <returns></returns>
        public List<StockOrderInfo> StockOrderInfoShowFalse()
        {
            string sql = "select StockOrderID,ProviderInfo.ProviderName,StorehouseName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from ";
            sql += " StockOrderInfo ";
            sql += " join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ";
            sql += " join StorehouseInfo on StockOrderInfo.StorehouseID=StorehouseInfo.StorehouseID where StockOrderState='False'";
            DataTable dt = helper.ExecDataTable(sql);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo StockOrderInfo = new StockOrderInfo();
                StockOrderInfo.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                StockOrderInfo.ProviderName = item["ProviderName"].ToString();
                StockOrderInfo.StorehouseName = item["StorehouseName"].ToString();
                StockOrderInfo.StockOrderTime = item["StockOrderTime"].ToString();
                StockOrderInfo.DocumentsCoding = item["DocumentsCoding"].ToString();
                StockOrderInfo.Remarks = item["Remarks"].ToString();
                StockOrderInfo.LastModified = item["LastModified"].ToString();
                list.Add(StockOrderInfo);
            }
            return list;
        }
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <returns></returns>
        public DataSet GetExcel()
        {
            string sql = "select StockOrderID,ProviderInfo.ProviderName,StorehouseName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from ";
            sql += " StockOrderInfo ";
            sql += " join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ";
            sql += " join StorehouseInfo on StockOrderInfo.StorehouseID=StorehouseInfo.StorehouseID where StockOrderState='False'";
            return helper.ExecDataSet(sql);
        }
    }
}